package com.bw.dao;

import com.bw.bean.Sst;
import com.bw.bean.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import javax.jws.WebService;
import java.util.List;

@Mapper
public interface UserDao {
    @Select("SELECT sum(amount) from order_info where  create_time BETWEEN '2023-01-06 00:00:00' and '2023-01-06 23:59:59'")
    Double list();
@Select("call ss;")
    List<User> list1();
@Select("SELECT q.*,rank()over(PARTITION by q.name ORDER BY q.ls desc) rap from \n" +
        "(SELECT b.org_name name,\n" +
        "count(org_name) cs,\n" +
        "sum(actual_distance) ls,\n" +
        "TIMESTAMPDIFF(SECOND,actual_start_time,actual_end_time) time,\n" +
        "(sum(actual_distance)/count(org_name)) avgls,\n" +
        "(sum(TIMESTAMPDIFF(SECOND,actual_start_time,actual_end_time))/count(org_name)) avgtime\n" +
        " from transport_task t \n" +
        "LEFT JOIN line_base_info l ON t.line_id=l.id\n" +
        "LEFT JOIN  base_organ b ON l.org_id= b.id\n" +
        "GROUP BY b.org_name) q\n")
    List<Sst> list3();
@Select("select r1.name,count(d.sorter_emp_id) value from transport_task_detail d \n" +
        "LEFT JOIN transport_task t on d.transport_task_id=t.id\n" +
        "LEFT JOIN line_base_info i on t.line_id=i.id\n" +
        "LEFT JOIN base_organ b on i.org_id=b.id\n" +
        "LEFT JOIN base_region_info r on b.region_id=r.id\n" +
        "LEFT JOIN base_region_info r1 on r.parent_id=r1.id\n" +
        "group by r1.name")
    List<User> list4();
}
